package org.hard.controller;

import lombok.SneakyThrows;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
import org.springframework.jdbc.datasource.init.ScriptUtils;

import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.nio.file.*;
import java.nio.file.attribute.BasicFileAttributes;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Date;
import java.util.Map;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;


/**
 * @author: 王林博
 * @date: 2023年02月18日 11:55
 */
public class ClearDBController {

//    @Autowired
    private static JdbcTemplate jdbcTemplate;

    private static long startTime = 0L;
    private static long endTime = 0L;
    private static ExecutorService executorService = Executors.newFixedThreadPool(2);


    static {
        DriverManagerDataSource dataSource=new DriverManagerDataSource();
        //连接mysql
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/chty?serverTimezone=CST");
        dataSource.setUsername("root");
        dataSource.setPassword("admin");
        jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public static void main(String[] args) throws SQLException {
        startTime = System.currentTimeMillis();
        System.out.println("---》开始时间:{}"+ startTime);
        while (delete() == 5000){}
        endTime =  System.currentTimeMillis();
        System.out.println("---》结束时间:{}"+ endTime);
        System.out.println(startTime - endTime);
    }

    public static int delete() throws SQLException {
        return jdbcTemplate.update("DELETE FROM chty_notify_user_10 where create_time < '2023-02-01 00:00:00' limit 5000");
    }



    // 批量执行sql
    public static void inster(String[] args) throws IOException {
        Files.walkFileTree(Paths.get("C:\\Users\\ASUS\\Desktop\\chty_notify_user-copy_split"),new SimpleFileVisitor<Path>(){
            @Override
            public FileVisitResult preVisitDirectory(Path dir, BasicFileAttributes attrs) throws IOException {
                System.out.println("---》进入文件夹"+dir);
                startTime = System.currentTimeMillis();
                System.out.println("---》开始时间:{}"+ System.currentTimeMillis());
                return super.preVisitDirectory(dir, attrs);
            }
            // 删除不走回收站，危险代码
            @Override
            public FileVisitResult visitFile(Path file, BasicFileAttributes attrs) throws IOException {
//                String fileName = file.getFileName().toString();
                if(file.getFileName().toString().startsWith("chty_notify_user-copy_")){

                    System.out.println(file.toString());
//                        executorService.execute(()->{
                            try {
                                runSqlBySpringUtils(file.toString());
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
//                        });
                }
//                System.out.println(file.getFileName());
//                Files.delete(file);
                return super.visitFile(file, attrs);
            }

            @SneakyThrows
            @Override
            public FileVisitResult postVisitDirectory(Path dir, IOException exc) throws IOException {
                // 线程不会再接收新的任务
//                executorService.shutdown();
//                while(true){
//                    // 判断子线程是否结束
//                    if(executorService.isTerminated()){
//                        System.out.println("所有的子线程都结束了！");
//                        break;
//                    }
//                    Thread.sleep(1000);
//                }
                System.out.println("《---退出文件夹"+dir);
                System.out.println("---》结束时间:{}"+ System.currentTimeMillis());
                endTime =  System.currentTimeMillis();
                System.out.println(startTime - endTime);

//                Files.delete(dir);
                return super.postVisitDirectory(dir, exc);
            }
        });
    }

    /**
     * 执行sql脚本文件 使用Spring工具类
     */
    public static void runSqlBySpringUtils(String pathUrl) throws Exception {
//        DriverManagerDataSource dataSource=new DriverManagerDataSource();
//        //连接mysql
//        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
//        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/chty?serverTimezone=CST");
//        dataSource.setUsername("root");
//        dataSource.setPassword("admin");
//        JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);
//        Map<String, Object> map = jdbcTemplate.queryForMap("select * from chty_notify_user limit 1");
//        System.out.println(map);
//        int update = jdbcTemplate.update("");
//        System.out.println(update);
        File file=new File(pathUrl);
        FileReader fileReader=new FileReader(file);
        BufferedReader bf=new BufferedReader(fileReader);
        StringBuffer sb=new StringBuffer();
        String line;
        while ((line = bf.readLine()) != null) {
            sb.append(line);
            sb.append("\n");
        }
//        System.out.println(sb);
        executeSqlScript(DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/chty?serverTimezone=CST","root","admin"),sb);
//        jdbcTemplate.execute(sb.toString());
//        Connection connection = jdbcTemplate.getDataSource().getConnection();
//        ScriptRunner runner = new ScriptRunner(connection);
//        runner.setAutoCommit(true);
//        File file = new File("xxxx.sql");
//        if (file.getName().endsWith(".sql")) {
//            runner.setFullLineDelimiter(false);
//            runner.setDelimiter(";");//语句结束符号设置
//            runner.setLogWriter(null);//日志数据输出，这样就不会输出过程
//            runner.setSendFullScript(false);
//            runner.setAutoCommit(true);
//            runner.setStopOnError(true);
//            runner.runScript(new InputStreamReader(new FileInputStream("xxx.sql"), "utf8"));
//        }
    }

    public static void executeSqlScript(Connection connection,StringBuffer sql)throws SQLException {
        try {
            connection.setAutoCommit(false);
            ScriptUtils.executeSqlScript(connection, new ByteArrayResource(sql.toString().getBytes()));
            connection.commit();
        } catch (SQLException e) {
            connection.rollback();
        }finally{
            connection.close();
        }
    }
}
